var express = require("express");
var uunewid = require("uuid");
var router = express.Router();
var constants = require("../utils/constants");
let log = require("cf-nodejs-logging-support");
log.setLoggingLevel("info");
var moment = require("moment");

const tableName = "SAP_JIANGDU_TECH_INNOS";
const tagTableName = "SAP_JIANGDU_TAG_TECHS";
const { ohana } = require("ohana-node-orm");
const { checkProperty, checkAuth } = require("../utils/utils");
var constants = require("../utils/constants");

router.get("/count", async (req, res, next) => {
  // #swagger.tags = ['科技']
  // #swagger.summary = '计数'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  let { USER_ID } = req.query;
  getCount(USER_ID)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function getCount(USER_ID) {
  let result = null;
  let ohana_instance = new ohana(tableName);
  if (USER_ID) {
    result = await ohana_instance.raw(
      "select count(*) as total from " +
        tableName +
        " where USER_ID_USER_ID = '" +
        USER_ID +
        "'"
    );
  } else {
    result = await ohana_instance.raw(
      "select count(*) as total from " + tableName
    );
  }
  return result[0];
}

router.get("/getTechStatistics", function (req, res, next) {
  const user = checkAuth(req.user, constants.BASICAUTH);
  if (!user.authCheck) {
    res.sendStatus(403);
  }
  getTechStatistics().then((result) => {
    res.send(result);
  });
});
async function getTechStatistics() {
  let ohana_instance = new ohana(tableName);
  let result = await ohana_instance.raw(
    "select TYPE, count(*) as TOTAL from " +
      tableName +
      " where TYPE is not NULL group by TYPE order by TOTAL desc  limit 5"
  );
  var resultData = {};
  resultData.topTech = result;

  //monthly growth
  var dateQuery =
    "select count(*) as TOTAL from " +
    tableName +
    " where CREATED_AT between '" +
    moment().days(-30).format("YYYY-MM") +
    "' and '" +
    moment().format("YYYY-MM") +
    "'";
  console.log(dateQuery);
  var monthlygrowth = await ohana_instance.raw(dateQuery);
  resultData.monthlygrowth = monthlygrowth[0].TOTAL;
  return resultData;
}

router.post("/", function (req, res, next) {
  // #swagger.tags = ['科技']
  // #swagger.summary = '获取tech'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/tech"
                    }  
                },
            }
    } */
  const userNew = checkAuth(req.user, constants.BASICAUTH);
  const { data } = req.body;
  const { type } = req.body;
  //如果是企业端或者 admin 调用接口
  const user = new ohana(tableName);
  if (!type || userNew.admin == true) {
    var condition = {};
    if (userNew.admin == false) {
      condition.STATUS = constants.STATUSON;
    }
    log.info(!data);
    if (!data.searchString) {
      if (data.TECH_ID) {
        user
          .findOne({ TECH_ID: data.TECH_ID })
          .then((result) => {
            if (result.length > 0) {
              if (
                result[0].COMPANY_NAME == userNew.company ||
                result[0].STATUS == constants.STATUSON
              ) {
                res.send(result);
              } else {
                res.send({
                  message: constants.MESSAGEWARNINGSTATUSOFF,
                  code: -1,
                });
              }
            } else {
              res.send({
                message: constants.MESSAGEWARNINGSTATUSOFF,
                code: -1,
              });
            }
          })
          .catch((err) => {
            log.error(err);
            res.status(500).send(err);
          });
      } else {
        const { page } = req.body.data;
        var sqlText = "select * from " + tableName;
        if (userNew.admin == false) {
          sqlText += " where STATUS = '" + constants.STATUSON + "'";
        }
        if (page) {
          const pageOffset = page.number * page.page;
          var pageText = " limit " + page.number + " offset " + pageOffset;
          sqlText += pageText;
        } else {
          sqlText += " limit 1000";
        }
        // console.log(sqlText);
        user
          .raw(sqlText)
          .then((result) => {
            res.send(result);
          })
          .catch((err) => {
            log.error(err);
            res.status(500).send(err);
          });
      }
    } else {
      var sqlText =
        "select * from " +
        tableName +
        " where (NAME like '%" +
        data.searchString +
        "%' or TEAM_TEAM_NAME like '%" +
        data.searchString +
        "%' or TEAM_BELONG_TO like '%" +
        data.searchString +
        "%' or TEAM_RESEARCH_FOCUS like '%" +
        data.searchString +
        "%' " +
        "or PRODUCT_PROJECT_GROUP like '%" +
        data.searchString +
        "%' or PRODUCT_RESEARCH_PROJECT like '%" +
        data.searchString +
        "%' or PRODUCT_PRODUCTION like '%" +
        data.searchString +
        "%'";
      if (userNew.admin == false) {
        sqlText += ") and STATUS = '" + constants.STATUSON + "'";
      }
      console.log(sqlText)
      user
        .raw(sqlText)
        .then((result) => {
          res.send(result);
        })
        .catch((err) => {
          log.error(err);
          res.status(500).send(err);
        });
    }
  } else if (type == constants.BACKEND && userNew.admin == false) {
    if (!data || !data.searchString) {
      var sqlCondition = {};
      if (userNew.level == constants.LEVELTWO) {
        sqlCondition = { USER_ID_USER_ID: userNew.user_id };
      } else {
        sqlCondition = { COMPANY_NAME: userNew.company };
      }
      user
        .find(sqlCondition)
        .then((result) => {
          res.send(result);
        })
        .catch((err) => {
          log.error(err);
          res.status(500).send(err);
        });
    } else {
      var sqlText = "";
      if (userNew.level == constants.LEVELTWO) {
        sqlText =
          "select * from " +
          tableName +
          " where NAME like '%" +
          data.searchString +
          "%'" +
          " and USER_ID_USER_ID = '" +
          userNew.user_id +
          "'";
      } else {
        sqlText =
          "select * from " +
          tableName +
          " where NAME like '%" +
          data.searchString +
          "%' or TEAM_TEAM_NAME like '%" +
          data.searchString +
          "%' or TEAM_BELONG_TO like '%" +
          data.searchString +
          "%' or TEAM_RESEARCH_FOCUS like '%" +
          data.searchString +
          "%' " +
          "or PRODUCT_PROJECT_GROUP like '%" +
          data.searchString +
          "%' or PRODUCT_RESEARCH_PROJECT like '%" +
          data.searchString +
          "%' or PRODUCT_PRODUCTION like '%" +
          data.searchString +
          "%'" +
          " and COMPANY_NAME = '" +
          userNew.company +
          "'";
        // sqlText =
        //   "select * from " +
        //   tableName +
        //   " where NAME like '%" +
        //   data.searchString +
        //   "%'" +
        //   " and COMPANY_NAME = '" +
        //   userNew.company +
        //   "'";
      }
      user
        .raw(sqlText)
        .then((result) => {
          res.send(result);
        })
        .catch((err) => {
          log.error(err);
          res.status(500).send(err);
        });
    }
  }
});

router.post("/add", function (req, res, next) {
  // #swagger.tags = ['科技']
  // #swagger.summary = '增加tech'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/tech_add_update"
                    }  
                },
            }
    } */
  const { data } = req.body;
  const user = checkAuth(req.user, constants.BASICAUTH);
  if (!user.authCheck) {
    res.sendStatus(403);
    return;
  }
  checkProperty(constants.TECHFIELDS, data);
  data.USER_ID_USER_ID = user.user_id;
  data.USER_NAME = user.user_name;
  data.TECH_ID = uunewid.v4();
  data.STATUS = constants.STATUSOFF; //创建状态默认为off
  if (!user.admin) {
    data.COMPANY_NAME = user.company ? user.company : "empty";
  }
  insertData(data)
    .then((result) => {
      if (result == 1) {
        res.status(200).send({ TECH_ID: data.TECH_ID });
      } else {
        log.info(result);
        res.status(500).send({ message: result });
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function insertData(body) {
  Object.keys(body).forEach((item) => {
    if (!body[item] || body[item] == "") delete body[item];
  });
  const user = new ohana(tableName);
  try {
    const result = await user.insert(body);
    return result;
  } catch (error) {
    console.log(error);
    log.error(error);
    return -1;
  }
}

/**
 * Get the company by criterian
 */
router.post("/search", function (req, res, next) {
  // #swagger.tags = ['科技']
  // #swagger.summary = '搜索tech，query参数未知'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  const queryData = req.query;
  const user = new ohana(tableName);
  if (queryData == null) {
    user
      .find()
      .then((result) => {
        res.send(result);
      })
      .catch((err) => {
        log.error(err);
        res.status(500).send(err);
      });
  } else {
    user
      .find(queryData)
      .then((result) => {
        res.send(result);
      })
      .catch((err) => {
        log.error(err);
        res.status(500).send(err);
      });
  }
});

/**update TECHs  */
router.put("/", function (req, res, next) {
  // #swagger.tags = ['科技']
  // #swagger.summary = '修改tech'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/tech_add_update"
                    }  
                },
            }
    } */
  //   checkData(res, req.body.data)
  const { data } = req.body;
  const user = checkAuth(req.user, constants.BASICAUTH);
  if (!user.authCheck) {
    res.sendStatus(403);
    return;
  }
  //checkProperty(constants.TECHFIELDS, data);
  //  log.info(data);
  data.UPDATED_AT = moment().format("YYYY-MM-DD");
  updateTech(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

/*delete something*/
router.delete("/", function (req, res, next) {
  // #swagger.tags = ['科技']
  // #swagger.summary = '删除tech'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/tech_delete"
                    }  
                },
            }
    } */
  const { data } = req.body;
  deleteTech(data)
    .then((result) => {
      log.info(result);
      if (result == 1) {
        res.sendStatus(200);
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function updateTech(body) {
  log.info(body);
  const policy = new ohana(tableName);
  const result = await policy.update(
    {
      TECH_ID: body.TECH_ID,
    },
    body
  );
  return result;
}
async function deleteTech(body) {
  const policy = new ohana(tableName);
  try {
    const result = await policy.raw(
      "delete from " + tableName + " where TECH_ID = '" + body.TECH_ID + "'"
    );
    policy.raw(
      "delete from " +
        constants.RECOMMENDLISTTABLE +
        " where RECOMMENDED_ID ='" +
        body.TECH_ID +
        "'"
    );
    policy.raw(
      "delete from " +
        constants.FAVORITETABLE +
        " where FAVOURITE_ID ='" +
        body.TECH_ID +
        "'"
    );
    return result;
  } catch (error) {
    log.error(error);
    return -1;
  }
}

router.post("/techTags", function (req, res, next) {
  // #swagger.tags = ['科技']
  // #swagger.summary = '获取标签'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/tech_tag"
                    }  
                },
            }
    } */
  const { data } = req.body;
  getTechTag(data)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.post("/addTags", function (req, res, next) {
  // #swagger.tags = ['科技']
  // #swagger.summary = '增加标签'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/tech_tag_add_delete"
                    }  
                },
            }
    } */
  const { data } = req.body;
  insertTechData(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else if (result == -1) {
        res.status(200).send({ code: -1, message: "请勿重复点击" });
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.delete("/deleteTags", function (req, res, next) {
  // #swagger.tags = ['科技']
  // #swagger.summary = '删除标签'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/tech_tag_add_delete"
                    }  
                },
            }
    } */
  const { data } = req.body;
  deleteTechTag(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

/**
 * 127.0.0.1:4000/api/users/getCompanyInfo
 * @param {*} body
 * @returns
 */
async function getTechTag(data) {
  const policyTag = new ohana(tagTableName); // new ohana('table_name');
  const result = await policyTag.raw(
    'select "TAG_ID", "TAG_NAME","TAG_VALUE" ,"TAG_CATEGORY" from "SAP_JIANGDU_TAGS" where "TAG_ID" in (Select "TAG_ID_TAG_ID" from "SAP_JIANGDU_TAG_TECHS" where "TECH_ID_TECH_ID" = \'' +
      data.TECH_ID +
      "' )"
  );
  return result;
}
async function insertTechData(data) {
  const policyTag = new ohana(tagTableName); // new ohana('table_name');
  const resultNum = await policyTag.find(data);
  if (resultNum.length > 0) {
    return -1;
  }
  const result = await policyTag.insert(data);
  return result;
}
async function deleteTechTag(data) {
  const policyTag = new ohana(tagTableName); // new ohana('table_name');
  const result = await policyTag.raw(
    'delete from "' +
      tagTableName +
      "\" where TECH_ID_TECH_ID = '" +
      data.TECH_ID_TECH_ID +
      "' and TAG_ID_TAG_ID = '" +
      data.TAG_ID_TAG_ID +
      "'"
  );
  return result;
}

module.exports = router;
